﻿using ProjectScheduling_web.model;
using SqlSugar;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;

namespace ProjectScheduling_web.dao
{
    public class BaseDao<T> where T : BClass, new()
    {
        private readonly IConfiguration _configuration;
        public BaseDao()
        {
            var builder = new ConfigurationBuilder()
              // 设置配置文件的基础路径，通常为类库所在的目录
              .SetBasePath(AppContext.BaseDirectory)
              .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
            _configuration = builder.Build();

            ConnectionConfig = new()
            {
                ConnectionString = _configuration["Connection"], //"Data Source=.;Initial Catalog=ProjectScheduling;Persist Security Info=True;User ID=sa;Password=a123456;",
                IsAutoCloseConnection = true,
                DbType = SqlSugar.DbType.SqlServer
            };
        }
        public SqlSugarClient Db
        {
            get
            {
                return new SqlSugarClient(ConnectionConfig);
            }
        }
        public ConnectionConfig ConnectionConfig { get; set; }

        public List<T> FindAll(Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Where(func).ToList();
        }
        public int Count(Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Count(func);
        }
        public List<T> FindAll(SearchQuery<T> query)
        {
            using var db = new SqlSugarClient(ConnectionConfig);

            var list = Search(db, query);
            return list.ToList();
        }
        public List<T> FindInCludeAll<T1>(SearchQueryIncludes<T, T1> query)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            ISugarQueryable<T> list = Search(db, query);
            if (query.Includes != null)
            {
                list = list.Includes(query.Includes);
            }
            return list.ToList();
        }
        public PageData<T> FindAll(int page, int pageSize, Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return new PageData<T>
            {
                Items = db.Queryable<T>().Where(func).OrderByDescending(it => it.Id).Skip((page - 1) * pageSize).Take(pageSize).ToList(),
                Total = db.Queryable<T>().Where(func).Count()
            };
        }


        private static ISugarQueryable<T> Search(SqlSugarClient db, SearchQuery<T> query)
        {
            ISugarQueryable<T> list = db.Queryable<T>();
            if (query != null)
            {
                if (query.Query != null)
                {
                    list = list.Where(query.Query);
                }
                if (query.OrderBy != null)
                {
                    if (query.IsDesc)
                    {
                        list = list.OrderByDescending(query.OrderBy);
                    }
                    else
                    {
                        list = list.OrderBy(query.OrderBy);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 分页并且排序
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="func">查询</param>
        /// <param name="orderby">排序字段</param>
        /// <param name="isDesc">是否倒叙</param>
        /// <returns></returns>
        public PageData<T> FindAll(int page, int pageSize, Expression<Func<T, bool>> func, Expression<Func<T, object>> orderby, bool isDesc = false)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            var list = Search(db, new SearchQuery<T>
            {
                Query = func,
                OrderBy = orderby,
                IsDesc = isDesc
            });
            list = list.Skip((page - 1) * pageSize).Take(pageSize);

            return new PageData<T>
            {
                Items = list.ToList(),
                Total = db.Queryable<T>().Where(func).Count()
            };
        }
        public PageData<T> FindAll(int page, int pageSize, SearchQuery<T> query)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            var list = Search(db, query);
            list = list.Skip((page - 1) * pageSize).Take(pageSize);
            ISugarQueryable<T> countQuery = db.Queryable<T>();
            if (query.Query != null)
            {
                countQuery = countQuery.Where(query.Query);
            }
            return new PageData<T>
            {
                Items = list.ToList(),
                Total = countQuery.Count()
            };
        }
        public T Find(int id)
        {
            return Find(it => it.Id == id);
        }
        public T Find(Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().First(func);
        }
        public int Add(T item)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Insertable(item).IgnoreColumns(it => it.Id).ExecuteReturnIdentity();
        }
        public int Update(T item)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Updateable(item).ExecuteCommand();
        }
        public int Update(T item, Expression<Func<T, object>> fields)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Updateable(item).UpdateColumns(fields).ExecuteCommand();
        }
        public int Delete(int id)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Deleteable<T>().Where(it => it.Id == id).ExecuteCommand();
        }
        public int Delete(Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Deleteable(func).ExecuteCommand();
        }
        public List<T> Group(Expression<Func<T, bool>> func, Expression<Func<T, object>> groupby)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Where(func).GroupBy(groupby).ToList();
        }
        public bool Exists(Expression<Func<T, bool>> func)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Any(func);
        }
        public DataTable ToTable(string sql, params (string key, object value)[] pars)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            var p = pars.Select(it => new SugarParameter(it.key, it.value)).ToList();
            return db.Ado.GetDataTable(sql, p);
        }
        /// <summary>
        /// 获取最小值
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="func"></param>
        /// <param name="property"></param>
        /// <returns></returns>
        public K GetMin<K>(Expression<Func<T, bool>> func, Expression<Func<T, K>> property)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Where(func).Min(property);
        }
        public K GetMax<K>(Expression<Func<T, bool>> func, Expression<Func<T, K>> property)
        {
            using var db = new SqlSugarClient(ConnectionConfig);
            return db.Queryable<T>().Where(func).Max(property);
        }
        public DataTable ListToDataTable(List<T> list)
        {
            var table = new DataTable();
            PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).ToArray();
            fields.ToList()
                .ForEach(field => table.Columns.Add(field.Name));

            foreach (var item in list)
            {
                var row = table.NewRow();
                foreach (var field in fields)
                {
                    row[field.Name] = field.GetValue(item);
                }
                table.Rows.Add(row);
            }
            return table;
        }
    }
}
